dbtにおける『SQLの記法』に関するベストプラクティス #dbt

dbtにおける『SQLの記法』に関するベストプラクティス #dbt

Clock Icon2024.03.31

アライアンス事業部 エンジニアグループ モダンデータスタック(MDS)チームのしんやです。

dbtはクラウド型データウェアハウス(DWH)におけるデータ変換に特化したツールです。非常に使い勝手が良く便利なツールである一方、様々な機能が提供されているのでいざ使ってみよう!となると『何をどうやって作り上げていけば良いんだろう?』『この場合のルールや制限はどういうものがあるの?どういう取り決めをもって扱えば良いんだろう?』という風に思うこともあるかと思います。(実際私自身そう感じました)

そんなユーザーの疑問や悩みを解決する、いわゆるdbtユーザー向けのガードレール的な存在となりうるコンテンツがdbt社から展開されています。それが『dbtベストプラクティスガイド(Best practice guides)』です。構造、スタイル、セットアップなど、dbt Labsの現在の視点を通した「ベストプラクティス」がまとめられています。

そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『SQLの書き方』に関するものを紹介し、読み解いていきたいと思います。

目次

 

SQLに関するスタイルガイド・記法

 

基本的な部分

 

フィールド、集約、グループ化

  • ✅ フィールドは集約やウィンドウ関数の前に記述してください。
  • ✅ 集計(Aggregations)は、パフォーマンスを向上させるために、他のテーブルに結合する前に、できるだけ早い段階で(できるだけ小さなデータセットで)実行すべきです。
  • 列名を列挙するよりも、番号(例:group by 1, 2)で並べたりグループ化したりする方が好ましいです。

 

結合(Joins)

  • ✅ 明示的に重複を削除するのでなければ、結合にはunion allを優先します。
  • ✅ 2つ以上のテーブルを結合する場合は、必ずカラム名の前にテーブル名を付けてください。1つのテーブルからのみ選択する場合は、接頭辞は必要ありません。
  • ✅ 結合する際は、都度joinの型を明示すべきです。
    • つまり、joinではなくinner joinと書く...ということです。
  • ❌ 結合条件にテーブルの別名(特に頭文字)を使用するのは避けましょう。
    • "customers "と比較して、"c "というテーブルを使うと、それが何であるかを理解するのが難しくなります。
  • ✅ 結合を推論しやすくするために、常に左から右に移動します。
    • right joinsは、多くの場合、どのテーブルから選択するか、どのテーブルに結合するかを変更する必要があることを示します。

 

共通テーブル式(CTE)のインポート

  • ✅ すべての{{ ref('...') }}文は、ファイル先頭のCTEに記述します。
  • ✅ CTEのインポートには、参照するテーブルの名前を付ける必要があります。
  • ✅ CTEでスキャンするデータをできるだけ制限する。可能であれば、実際に使用する列のみを選択し、where句を使用して不要なデータをフィルタリングします。
  • 記述例:
    with
    
    orders as (
    
        select
            order_id,
            customer_id,
            order_total,
            order_date
    
        from {{ ref('orders') }}
    
        where order_date >= '2020-01-01'
    
    )
    

 

共通テーブル式(CTE)の実装

  • ✅ パフォーマンスが許す限り、CTE は単一の論理的な作業単位を実行すべきです。
  • ✅ CTEの名前は、その機能を伝えるために必要なだけ冗長である必要があります。
    • user_eventsの代わりにevents_joined_to_usersを使用しましょう。
      • これは適切なモデル名かもしれませんが、特定の関数や変換を説明するものではありません。
  • ✅ モデル間で重複する CTEは、独自の中間モデルに抽出する必要があります。
    • 独自のモデルにリファクタリングする必要がある、繰り返されるロジックの塊に注意してください。
  • ✅ モデルの最後の行は、最終出力されるCTEからのselect *である必要があります。
    • これにより、モデルの開発中に、モデル内のさまざまなステップからの出力を具体化して監査することが簡単になります。
    • selectステートメントで参照されている CTEを変更するだけで、そのステップからの出力が表示されます。

 

モデルに関する設定

  • ✅ モデル固有の属性(sort/distキーなど)は、モデルで指定されるべきです。
  • ✅ 特定の設定がディレクトリ内のすべてのモデルに適用される場合は、dbt_project.ymlファイルで指定する必要があります。
  • ✅ モデル内の設定については、最大限の可読性を得るために以下のような指定とすべきです。
{{
    config(
      materialized = 'table',
      sort = 'id',
      dist = 'id'
    )
}}

 

SQLサンプル例

上述のルールを踏まえたSQLモデルの記述サンプルは以下のようなものとなります。

with

events as (

    ...

),

{# CTE comments go here #}
filtered_events as (

    ...

)

select * from filtered_events
with

my_data as (

    select
        field_1,
        field_2,
        field_3,
        cancellation_date,
        expiration_date,
        start_date

    from {{ ref('my_data') }}

),

some_cte as (

    select
        id,
        field_4,
        field_5

    from {{ ref('some_cte') }}

),

some_cte_agg as (

    select
        id,
        sum(field_4) as total_field_4,
        max(field_5) as max_field_5

    from some_cte

    group by 1

),

joined as (

    select
        my_data.field_1,
        my_data.field_2,
        my_data.field_3,

        -- use line breaks to visually separate calculations into blocks
        case
            when my_data.cancellation_date is null
                and my_data.expiration_date is not null
                then expiration_date
            when my_data.cancellation_date is null
                then my_data.start_date + 7
            else my_data.cancellation_date
        end as cancellation_date,

        some_cte_agg.total_field_4,
        some_cte_agg.max_field_5

    from my_data

    left join some_cte_agg
        on my_data.id = some_cte_agg.id

    where my_data.field_1 = 'abc' and
        (
            my_data.field_2 = 'def' or
            my_data.field_2 = 'ghi'
        )

    having count(*) > 1

)

select * from joined

 

まとめ

という訳で、dbtにおけるベストプラクティスの1つ『SQLの記法に関するベストプラクティス』の紹介でした。

合わせて投稿した『***』同様、この辺りのポイントはdbtでモデル開発をしていくうえでの『前提』として身に付けておくべきものと位置付けることが出来ます。『dbtの流儀』として早目に慣れておきたいところですね。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.